package com.conver;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import com.bean.ColumnBean;
import com.bean.TableBean;
import com.util.CommonUtil;
import com.util.MysqlUtil;
import com.util.OracleUtil;

/**
 * 将Oracle数据迁移到MySQL
 * @author PF0P65Z6
 *
 */
public class InsertDataToMySQL {
    public static void main(String[] args) throws Exception {
    	long start = System.currentTimeMillis();
    	
    	int count = 0;
    	List<TableBean> tableList = OracleUtil.getAllTableName();
    	for(TableBean tableBean : tableList){
    		 insertDataToMysql(tableBean.getTableName());
    		 count++;
    	}
    	
    	long end = System.currentTimeMillis();
    	System.out.println("oracle表数据迁移到mysql已完成，总共涉及表"+count+"张，用时"+(end-start)/1000+"秒");
    }
    
    public static void insertDataToMysql(String tableName) {
    	long start = System.currentTimeMillis();
        List<ColumnBean> list = OracleUtil.getColList(tableName);
        Connection conn = null;
        PreparedStatement pstm = null;

        StringBuffer sb = new StringBuffer();
        sb.append("INSERT INTO ").append(tableName.toUpperCase()).append("(");
        for (int i = 0; i < list.size(); i++) {
            ColumnBean c = list.get(i);
            sb.append(c.getColumnName() + ",");
        }
        sb = sb.deleteCharAt(sb.length() - 1);
        sb.append(") values ( ").append(CommonUtil.formatString("?", list.size())).append(")");

        //获取所有oracle列数据
        ResultSet rs = OracleUtil.getDataFromTable(tableName);
        try {
            conn = MysqlUtil.getConnection();
            conn.setAutoCommit(false);
            pstm = conn.prepareStatement(sb.toString());
            int count = 0;
            while(rs.next()) {
                count ++;
                for (int i = 1; i <= list.size(); i++) {
                    ColumnBean c = list.get(i-1);
                    setValue(pstm,rs,i,c);
                }

                pstm.addBatch();
                if(count %  1000 == 0 || rs.isLast()){
                    pstm.executeBatch();
                    conn.commit();
                    pstm.clearBatch();
                    System.out.println("表"+tableName+"正在更新,已更新"+count+"条!");
                }
            }
            
            long end = System.currentTimeMillis();
            System.out.println("表"+tableName+"，已更新完成!"+"oracle表数据构迁移到mysql，总共更新"+count+"条，用时"+(end-start)/1000+"秒");
        } catch (SQLException e) {
            e.printStackTrace();
        }finally{
        	MysqlUtil.releaseDB(pstm);
        }
    }
    
    private static void setValue(PreparedStatement pstm, ResultSet rs,int i, ColumnBean c) throws SQLException{
        String type = c.getColumnType();
        String name = c.getColumnName();
        if("NUMBER".equalsIgnoreCase(type)){
            if(c.getDataScale()>0){
                pstm.setDouble(i, rs.getDouble(name));
            }else{
                pstm.setInt(i,rs.getInt(name));
            }
        }else if("DATE".equalsIgnoreCase(type)){
            pstm.setDate(i, rs.getDate(name));
        }else if("BLOB".equalsIgnoreCase(type)){
        	pstm.setString(i, CommonUtil.blobToString(rs.getBlob(name)));
        }else if("CLOB".equalsIgnoreCase(type)){
        	pstm.setString(i, CommonUtil.clobToString(rs.getClob(name)));
        }else{
            pstm.setString(i, rs.getString(name));
        }
    }
}